haxefandomcom-20200215-history
MySQL Tutorial (Neko)
There is several API already provided with Haxe. One of the useful ones enables you to access MySQL databases very easily. = Connection to the Database = The first step is to connect to the database. Here's a sample that shows how to establish a connection: class Test { static function main() { var cnx = neko.db.Mysql.connect({ host : "localhost", port : 3306, user : "root", pass : "", socket : null, database : "MyBase" }); // ... cnx.close(); } } This will establish a connection on localhost:3306 with user root and empty password. You can also use a socket on Unix systems to connect to the database. If you want to connect using the standard way, set socket to null. Once the connection is established, a /api/neko/db/Connection object is returned. You can use it to select the database on which you want to operate. After you're done with using the connection, you can simply close it. = Request and Result = You can execute a request using the cnx.request("QUERY") method. This will return a /api/neko/db/ResultSet containing all the rows. Here's an example : var rset = cnx.request("SELECT * FROM Users"); neko.Lib.print("Found "+rset.length+" users"); for( row in rset ) { neko.Lib.print("User "+row.name+" is "+row.age+" years old "); } If the field you select does not have a name, you can use the get methods from the result set : var r = cnx.request("SELECT COUNT(*), SUM(money) FROM Users WHERE age >= 18"); neko.Lib.print("Users count = " + r.getIntResult(0)); neko.Lib.print("Total money = " + r.getFloatResult(1)); In the case of an "INSERT", UPDATE or "DELETE" request, the length with tell the number of rows affected* : var rset = cnx.request("UPDATE Users SET age = age + 1"); neko.Lib.print("Updated "+rset.length+" users"); (*) This similar to php's mysql_affected_rows = Type Conversions = When you execute a request, the rows in the ResultSet are anonymous objects containing table values. The MySQL types are automaticaly converted to the corresponding Haxe types : MySQL Haxe ------------------------------- TINYINT(1) Bool TINYINT Int SHORT Int LONG Int INT24 Int LONGLONG Float DECIMAL Float FLOAT Float DOUBLE Float DATE Date DATETIME Date BLOB String* other String (*) : can contain \0 character = Escaping = If you want to use Strings inside your SQL requests, don't forget to escape them so they can't be forged to execute additional SQL. For example : var r = cnx.request("SELECT * FROM Users WHERE name = '"+cnx.escape(name)+"'"); Or by using the quote method : var r = cnx.request("SELECT * FROM Users WHERE name = "+cnx.quote(name)); = Possible problems = **Error: **Exception : Neko_error(mysql.c(438) : Failed to connect to mysql server : Client does not support authentication protocol requested by server; consider upgrading MySQL client) **Cause: **MySQL 4.1 and up uses an authentication protocol based on a password hashing algorithm that is incompatible with older clients. **Solution: **Run mysql and login as root. mysql -uroot -p Then update your mysql user (change somepassword, someuser, somehost as needed) UPDATE `mysql`.`user` SET `password`=OLD_PASSWORD('somepasswod') WHERE `user`='someuser' AND `host`='somehost'; flush privileges; = See = See the following API references pages : * /api/neko/db/Mysql * /api/neko/db/Connection * /api/neko/db/ResultSet You can also read the /doc/neko/spod Tutorial for a more highlevel API. Category:Neko Category:MySQL Category:Tutorials Category:Networking